
import pandas as pd
import matplotlib as plt
import seaborn as sb
import requests
import gspread
import time
from gspread_dataframe import get_as_dataframe, set_with_dataframe
from datetime import date
import plotly.express as px
import warnings
import plotly.graph_objects as go
import random
random.seed(0)
warnings.filterwarnings('ignore')


COMMANDES = "commandes_all"
DEPENSES = "depenses_all"
DETTES = "paiement_dette_all"
PRODUCTION = "production_all"
STOCK = "stock_all"


gc = gspread.service_account(filename = "/Users/eliemas/Documents/Ishiraku/keys/ishiraku_analytics_key.json")


commandes_spreadsheet = gc.open(COMMANDES)
depenses_spreadsheet = gc.open(DEPENSES)
dettes_spreadsheet = gc.open(DETTES)
production_spreadsheet = gc.open(PRODUCTION)
stock_spreadsheet = gc.open(STOCK)


month_name = ["janvier", "fevrier", "mars", "avril", "mais",
              "juin", "juillet", "aout", "septembre", "octobre", "novembre", "decembre"]


# today = str(date.today())
# year, month, day = today.split("-")
# month_letters = month_name[int(month) - 1]

month_letters = "aout"
month = "08"
year = "2022"
start_day = 1
end_day = 31


# period = 6


# start_day = int(day) - period
# end_day = int(day)


commandes_df = pd.DataFrame()


for day in range(start_day, end_day + 1):
    day = str(day)
    if len(day) < 2:
        day = "0" + day
    worsheet_name = day + "_" + month_letters + "_" + year
    temp_df = pd.DataFrame(commandes_spreadsheet.worksheet(worsheet_name).get_all_records())
    _date_ = year + "-" + month + "-" + day
    temp_df["date"] = pd.to_datetime(_date_)
    temp_df["CLIENTS"] = temp_df["CLIENTS"].str.strip()
    commandes_df = commandes_df.append(temp_df, ignore_index=True)
    time.sleep(2)


commandes_df.tail(10)


commandes_df.dtypes

CLIENTS                object
COMMANDES             float64
PAYES                  object
COMMENTAIRE            object
TYPE_CLIENT            object
date           datetime64[ns]
dtype: object


commandes_df['TYPE_CLIENT'].isnull().values.any()

False


commandes_df['CLIENTS'].isnull().values.any()

False


commandes_df['PAYES'].isnull().values.any()

False


def clean(x):
    if x == '':
        return 0
    return float(x)


commandes_df['PAYES'] = commandes_df['PAYES'].apply(lambda x: clean(x))
commandes_df["COMMANDES_USD"] = commandes_df['COMMANDES'].apply(lambda x: x * 0.00049)
commandes_df["PAYES_USD"] = commandes_df['PAYES'].apply(lambda x: x * 0.00049)


commandes_df.dtypes

CLIENTS                  object
COMMANDES               float64
PAYES                   float64
COMMENTAIRE              object
TYPE_CLIENT              object
date             datetime64[ns]
COMMANDES_USD           float64
PAYES_USD               float64
dtype: object


commandes_per_client = commandes_df.groupby(by=["CLIENTS"]).sum()
commandes_per_client.reset_index(inplace=True)


commandes_per_client = commandes_per_client.sort_values(by='COMMANDES_USD', ascending=False)


commandes_per_client.head(5)


fig = px.bar(commandes_per_client, x='CLIENTS', y='COMMANDES_USD', title="Total Bread Orders per Client")
fig.show()


commandes_per_client_types = commandes_df.groupby(by=["TYPE_CLIENT"]).sum()
commandes_per_client_types.reset_index(inplace=True)
commandes_per_client_types = commandes_per_client_types.sort_values(by='COMMANDES_USD', ascending=False)


fig = px.bar(commandes_per_client_types, x='TYPE_CLIENT', y='COMMANDES_USD', title="Total Bread Orders per Client Types")
fig.show()


fig = px.pie(commandes_per_client_types, values='COMMANDES_USD', names='TYPE_CLIENT', title="Total Bread Orders per Client Types")
fig.show()


commandes_payes_df_per_client = commandes_df.groupby(by=["CLIENTS"]).sum()
commandes_payes_df_per_client.reset_index(inplace=True)
commandes_payes_df_per_client = commandes_payes_df_per_client.sort_values(by='PAYES_USD', ascending=False)


commandes_payes_df_per_client.head(5)


fig = px.bar(commandes_payes_df_per_client, x='CLIENTS', y='PAYES_USD', title="Total Paid for Bread Orders per Client Types")
fig.show()


commandes_payes_per_client_types = commandes_df.groupby(by=["TYPE_CLIENT"]).sum()
commandes_payes_per_client_types.reset_index(inplace=True)
commandes_payes_per_client_types = commandes_payes_per_client_types.sort_values(by='PAYES_USD', ascending=False)


fig = px.bar(commandes_payes_per_client_types, x='TYPE_CLIENT', y='PAYES_USD', title="Total Paid for Bread Orders per Client Types")
fig.show()


fig = px.pie(commandes_payes_per_client_types, values='PAYES_USD', names='TYPE_CLIENT', title="Total Paid for Bread Orders per Client Types")
fig.show()


commandes_payes_df_per_client["RATIO"] = (commandes_payes_df_per_client["PAYES_USD"] / commandes_payes_df_per_client["COMMANDES_USD"]) * 100
commandes_ratio_per_client = commandes_payes_df_per_client.sort_values(by='RATIO', ascending=False)


commandes_ratio_per_client.head(5)


fig = px.bar(commandes_ratio_per_client, x='CLIENTS', y='RATIO', title="Ratio of Bread orders value and Money paid for Bread Orders Per Client")
fig.show()


commandes_payes_per_client_types["RATIO"] = (commandes_payes_per_client_types["PAYES_USD"] / commandes_payes_per_client_types["COMMANDES_USD"]) * 100
commandes_ratio_per_client_types = commandes_payes_per_client_types.sort_values(by='RATIO', ascending=False)


commandes_ratio_per_client_types


fig = px.bar(commandes_ratio_per_client_types, x='TYPE_CLIENT', y='RATIO', title="Ratio of Bread orders value and Money paid for Bread Orders Per Client type")
fig.show()


worksheet_name = "paiement_dette_" + month_letters
dettes_df = pd.DataFrame(dettes_spreadsheet.worksheet(worksheet_name).get_all_records())
dettes_df["date"] = pd.to_datetime(dettes_df["date"])
dettes_df["CLIENTS"] = dettes_df["client"]
dettes_df = dettes_df.drop(columns=["client", "date"])
dettes_df["DETTES_PAYES_USD"] = dettes_df['montant'].apply(lambda x: x * 0.00049)


# QUICK FIX ; TO REMOVE
dettes_df["CLIENTS"] = dettes_df["CLIENTS"].apply(lambda x: x.strip())
dettes_df = dettes_df.apply(lambda x: x.replace({'RUTH':'DP RUTH', 'THEO':'DP THEO','DP THÉO':'DP THEO','CELITA':'DP CELITA','NSONA': 'DP NSONA','MAPASA': 'DP MAPASA'}, regex=False))


dettes_df


dettes_df = dettes_df.groupby(by=["CLIENTS"]).sum()
dettes_df.reset_index(inplace=True)


dettes_df.head(5)


commandes_payes_df_per_client.head(10)


dettes_unpaid_df = pd.merge(commandes_payes_df_per_client, dettes_df, how='inner', on = 'CLIENTS')


dettes_unpaid_df.head(5)


dettes_unpaid_df["DETTES_UNPAID_USD"] = dettes_unpaid_df["COMMANDES_USD"] - (dettes_unpaid_df["DETTES_PAYES_USD"] + dettes_unpaid_df["PAYES_USD"])
dettes_unpaid_df = dettes_unpaid_df.sort_values(by='DETTES_UNPAID_USD', ascending=False)


fig = px.bar(dettes_unpaid_df, x='CLIENTS', y='DETTES_UNPAID_USD', title="Total Unpaid Debts Per Client")
fig.show()


depenses_df = pd.DataFrame()


for day in range(start_day, end_day + 1):
    day = str(day)
    if len(day) < 2:
        day = "0" + day
    worsheet_name = day + "_" + month_letters + "_" + year
    temp_df = pd.DataFrame(depenses_spreadsheet.worksheet(worsheet_name).get_all_records())
    _date_ = year + "-" + month + "-" + day
    temp_df["date"] = pd.to_datetime(_date_) 
    depenses_df = depenses_df.append(temp_df, ignore_index=True)
    time.sleep(2)


depenses_df.head(5)


depenses_df["montant"] = depenses_df["montant"].apply(lambda x: clean(x))


depenses_df.dtypes

raison                     object
montant                   float64
quantite                   object
justification              object
qui_sort_argent            object
date               datetime64[ns]
dtype: object


depenses_df_reduce = depenses_df.copy()
depenses_df_reduce["raison"] = depenses_df_reduce["raison"].str.upper()
depenses_df_reduce = depenses_df_reduce.groupby(by=["raison"]).sum()
depenses_df_reduce.reset_index(inplace=True)
depenses_df_reduce = depenses_df_reduce.sort_values(by='montant', ascending=False)
depenses_df_reduce["montant_USD"] = depenses_df_reduce['montant'].apply(lambda x: x * 0.00049)
depenses_df_reduce.head(10)


fig = px.bar(depenses_df_reduce, x='raison', y='montant_USD', title= "Expenses per expenses reason")
fig.show()


depenses_df_reduce_top10 = depenses_df_reduce.head(10)
fig = px.pie(depenses_df_reduce_top10, values='montant_USD', names='raison', title="Top 10 Expenses per expenses reason")
fig.show()


commandes_payes_df_per_client.head(5)


commandes_df.head(3)


commandes_df_reduced = commandes_df[["CLIENTS", "TYPE_CLIENT"]]


remises_df = pd.merge(commandes_payes_df_per_client, commandes_df_reduced, how='left', on = 'CLIENTS')


dettes_unpaid_df.head(5)


dettes_reduced_df = dettes_unpaid_df[["CLIENTS", "DETTES_PAYES_USD"]]


remises_df = pd.merge(remises_df, dettes_reduced_df, how='left', on = 'CLIENTS')


remises_df["DETTES_PAYES_USD"] = remises_df["DETTES_PAYES_USD"].fillna(0)


remises_df["TOTAL_PAID_USD"] = remises_df["PAYES_USD"] + remises_df["DETTES_PAYES_USD"]


remises_df.head(5)


remises_df["REMISE_USD"] = [remises_df["TOTAL_PAID_USD"][i] * 0.32 if remises_df["TYPE_CLIENT"][i] 
                              in ["DEPOT_CASH", "DEPOT"]  else remises_df["TOTAL_PAID_USD"][i] * 0.27 for i in range(len(remises_df))]


remises_df = remises_df.drop_duplicates()
remises_df = remises_df.sort_values(by='REMISE_USD', ascending=False)
remises_df.head()


fig = px.bar(remises_df, x='CLIENTS', y='REMISE_USD', title= "Total Remises per Client")
fig.show()


remises_df_top_30 = remises_df.head(30)
fig = px.pie(remises_df_top_30, values='REMISE_USD', names='CLIENTS', title="Top 30 Total Remises per Client",
            custom_data=['CLIENTS', 'TYPE_CLIENT', 'REMISE_USD'])
fig.update_traces(
    hovertemplate="<br>".join([
        "CLIENTS: %{customdata[0][0]}",
        "TYPE_CLIENT: %{customdata[0][1]}",
        "REMISE_USD: %{customdata[0][2]}",
    ])
)
fig.show()


total_commandes = commandes_df["COMMANDES_USD"].sum()
total_commandes_paid = commandes_df["PAYES_USD"].sum()
total_debts_paid = dettes_unpaid_df["DETTES_PAYES_USD"].sum()
total_debts_unpaid = dettes_unpaid_df["DETTES_UNPAID_USD"].sum()
total_expenses = depenses_df_reduce["montant_USD"].sum()
total_remises = remises_df["REMISE_USD"].sum()
total_paid = remises_df["TOTAL_PAID_USD"].sum()
total_gain = total_commandes - total_remises - total_expenses


fig = go.Figure()

fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_commandes,
    title = {'text': "Total Breads Ordered in USD"},
    domain = {'row': 0, 'column': 0}
))

fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_commandes_paid,
    title = {'text': "Total Paid for Breads Ordered in USD"},
    domain = {'row': 0, 'column': 1}
))
fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_debts_paid,
    title = {'text': "Total Debts Paid in USD"},
    domain = {'row': 1, 'column': 0}
))
fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_debts_unpaid,
    title = {'text': "Total Debts Unpaid in USD"},
    domain = {'row': 1, 'column': 1}
))

fig.update_layout(
    grid = {'rows': 2, 'columns': 2, 'pattern': "independent"},)


fig = go.Figure()

fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_expenses,
    title = {'text': "Total Expenses in USD"},
    domain = {'row': 0, 'column': 0}
))

fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_remises,
    title = {'text': "Total Remises in USD"},
    domain = {'row': 0, 'column': 1}
))
fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_paid,
    title = {'text': "Total Paid for Breads in USD (counting debts)"},
    domain = {'row': 1, 'column': 0}
))
fig.add_trace(go.Indicator(
    mode = "gauge+number",
    value = total_gain,
    title = {'text': "Total Gain/Deficit in USD"},
    domain = {'row': 1, 'column': 1}
))

fig.update_layout(
    grid = {'rows': 2, 'columns': 2, 'pattern': "independent"},)

	CLIENTS	COMMANDES	PAYES	TYPE_CLIENT	date
863	MORIS	2000.0	2000	MAMAN	2022-08-31
864	DP RUTH	94900.0	0	DEPOT	2022-08-31
865	DP CELITA	119500.0	0	DEPOT_CASH	2022-08-31
866	DP MAPASA	182800.0	124300	DEPOT	2022-08-31
867	DP NSONA	59000.0	0	DEPOT	2022-08-31
868	DP DEBY	164000.0	111550	DEPOT_CASH	2022-08-31
869	DP MIMI	93000.0	63250	DEPOT_CASH	2022-08-31
870	DP THÉO	74400.0	27500	DEPOT_CASH	2022-08-31
871	DPR1	215300.0	127350	DEPOT_RELAIS_MAMAN	2022-08-31
872	DPR2	125500.0	89800	DEPOT_RELAIS_MAMAN	2022-08-31

	CLIENTS	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD
28	DP RUTH	3966500.0	379100.0	1943.585	185.7590
18	DP DEBY	3351600.0	2333500.0	1642.284	1143.4150
19	DP MAPASA	2803800.0	1505250.0	1373.862	737.5725
32	DPR1	2681600.0	1954100.0	1313.984	957.5090
17	DP CELITA	2348300.0	25900.0	1150.667	12.6910

	CLIENTS	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD
18	DP DEBY	3351600.0	2333500.0	1642.284	1143.4150
32	DPR1	2681600.0	1954100.0	1313.984	957.5090
33	DPR2	2203600.0	1852300.0	1079.764	907.6270
26	DP R1	2259600.0	1637150.0	1107.204	802.2035
19	DP MAPASA	2803800.0	1505250.0	1373.862	737.5725

	CLIENTS	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD	RATIO
2	AIMEE	498500.0	514100.0	244.265	251.909	103.129388
44	MIREILLE	57800.0	57800.0	28.322	28.322	100.000000
55	RIVIERE	27500.0	27500.0	13.475	13.475	100.000000
16	DORCAS	66500.0	66500.0	32.585	32.585	100.000000
48	NATHAN	20000.0	20000.0	9.800	9.800	100.000000

	TYPE_CLIENT	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD	RATIO
4	MAMAN_CASH	55400.0	47400.0	27.146	23.2260	85.559567
2	DEPOT_RELAIS_MAMAN	9074300.0	7013050.0	4446.407	3436.3945	77.284749
3	MAMAN	4364100.0	2930600.0	2138.409	1435.9940	67.152448
1	DEPOT_CASH	11773500.0	5634550.0	5769.015	2760.9295	47.857901
0	DEPOT	9409800.0	2016950.0	4610.802	988.3055	21.434568

Finances Summary¶

Import Libraries¶

Data Tables¶

Service¶

Fetch Data Tables Spreadsheet¶

Get Lookout Period for Tables Spreadsheet¶

Get Tables for lookout period¶

1. Commandes¶

Total Bread Orders per Client¶

Total Bread Orders per Client Types¶

Total Paid for Bread Orders per Client¶

Total Paid for Bread Orders per Client Types¶

Ratio of Bread Orders value and Money Paid for Bread Orders Per Client¶

Ratio of Bread Orders value vs Money Paid for Bread Orders Per Client Types¶

Total Unpaid Debt per Client¶

Total Expenses per Expenses reason¶

Total "Remises" per Clients¶

Total Summaries USD¶

	montant	receveur	CLIENTS	DETTES_PAYES_USD
0	5000.0	ASKA	CHRISTINE	2.450
1	3000.0	ASKA	CHRISTINE	1.470
2	3000.0	ASKA	CHRISTINE	1.470
3	12800.0	ASKA	CHRISTINE	6.272
4	155000.0	JASON	DP RUTH	75.950
...	...	...	...	...
193	100000.0	CHADRACK	DP RUTH	49.000
194	0.0		CHRISTINE	0.000
195	100000.0	CHADRACK	DP MAPASA	49.000
196	58500.0	CHADRACK	DP NSONA	28.665
197	24900.0	CHADRACK	DP THEO	12.201

	CLIENTS	montant	DETTES_PAYES_USD
0	ARIELLE	1000.0	0.4900
1	BILA	4000.0	1.9600
2	CHRISTINE	341600.0	167.3840
3	DEBY	300.0	0.1470
4	DP CELITA	1687950.0	827.0955

	raison	montant	quantite	date
0	Ration	10000.0		2022-08-01
1	PILE	1500.0		2022-08-01
2	Transport DP4	0.0		2022-08-01
3	Transport travailleurs	18000.0		2022-08-01
4	levure	13500.0	3	2022-08-01

	raison	montant	montant_USD
20	FARINE	18875400.0	9248.946
44	REMISE	1173300.0	574.917
56	TRANSPORT DP	1000800.0	490.392
5	BOIS	650000.0	318.500
32	LEVURE	631000.0	309.190
54	TRANSPORT	588000.0	288.120
47	SACHETS	504500.0	247.205
24	HUILE	459800.0	225.302
50	SUCRE	391000.0	191.590
43	RATION	370000.0	181.300

	CLIENTS	COMMANDES	PAYES	TYPE_CLIENT	date	COMMANDES_USD	PAYES_USD
0	ODILE	15500.0	15500.0	MAMAN	2022-08-01	7.595	7.595
1	AIMEE	15000.0	15000.0	MAMAN	2022-08-01	7.350	7.350
2	ESPERANCE	3000.0	3000.0	MAMAN	2022-08-01	1.470	1.470

	CLIENTS	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD	RATIO	montant	DETTES_PAYES_USD
0	DP MAPASA	2803800.0	1505250.0	1373.862	737.5725	53.686069	387800.0	190.022
1	EDITH	447500.0	441000.0	219.275	216.0900	98.547486	3000.0	1.470
2	DP RUTH	3966500.0	379100.0	1943.585	185.7590	9.557544	3512000.0	1720.880
3	DP THEO	1415100.0	283450.0	693.399	138.8905	20.030387	757700.0	371.273
4	DEBY	132000.0	89500.0	64.680	43.8550	67.803030	300.0	0.147

	CLIENTS	COMMANDES	PAYES	COMMANDES_USD	PAYES_USD	RATIO	montant	DETTES_PAYES_USD	DETTES_UNPAID_USD
0	DP MAPASA	2803800.0	1505250.0	1373.862	737.5725	53.686069	387800.0	190.0220	446.2675
8	DP CELITA	2348300.0	25900.0	1150.667	12.6910	1.102926	1687950.0	827.0955	310.8805
3	DP THEO	1415100.0	283450.0	693.399	138.8905	20.030387	757700.0	371.2730	183.2355
7	LINA	904400.0	30000.0	443.156	14.7000	3.317116	529000.0	259.2100	169.2460
10	CHRISTINE	509200.0	7400.0	249.508	3.6260	1.453260	341600.0	167.3840	78.4980